Amazon Redshift これでデータレイクの連携がスムーズになる！ UNLOADコマンドがヘッダー行に対応したので試してみました
はじめに
UNLOADコマンドでHEADERオプションを指定すると、作成された各出力ファイルに特定のヘッダー行を追加できるようになりました。ヘッダー行には、UNLOADクエリによって作成されたカラム名が含まれています。では早速試してみましょう。
検証テーブルとデフォルトのUNLOAD
例えば、以下のようなテーブルがあったとします。
CREATE TABLE IF NOT EXISTS "cm_schema"."orders"
(
"row_id" BIGINT ENCODE lzo
,"order_id" VARCHAR(255) ENCODE raw
,"order_date" DATE ENCODE lzo
,"ship_date" DATE ENCODE lzo
,"ship_mode" VARCHAR(255) ENCODE lzo
,"customer_id" VARCHAR(255) ENCODE lzo
,"customer_name" VARCHAR(255) ENCODE lzo
,"segment" VARCHAR(255) ENCODE lzo
,"country" VARCHAR(255) ENCODE lzo
,"city" VARCHAR(255) ENCODE lzo
,"state" VARCHAR(255) ENCODE lzo
,"region" VARCHAR(255) ENCODE lzo
,"product_id" VARCHAR(255) ENCODE lzo
,"category" VARCHAR(255) ENCODE lzo
,"sub_category" VARCHAR(255) ENCODE lzo
,"product_name" VARCHAR(255) ENCODE lzo
,"sales" DOUBLE PRECISION
,"quantity" BIGINT ENCODE lzo
,"discount" DOUBLE PRECISION
,"profit" DOUBLE PRECISION
)
DISTKEY("order_id")
SORTKEY (
"order_date"
)
;
cmdb=> select * from orders limit 1;
-[ RECORD 1 ]-+---------------------------
row_id | 1
order_id | JP-2016-1000099
order_date | 2016-11-08
ship_date | 2016-11-08
ship_mode | 即日配送
customer_id | 谷大-14605
customer_name | 谷奥 大地
segment | 消費者
country | 日本
city | 千歳
state | 北海道
region | 北海道
product_id | 家具-本棚-10004817
category | 家具
sub_category | 本棚
product_name | Dania キャビネット, 従来型
sales | 16974
quantity | 3
discount | 0.4
profit | -1986
まずは従来通りUNLOADコマンドを実行します。
UNLOAD ('SELECT * FROM cm_schema.orders')
TO 's3://cm-bucket/orders_unload/'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/cm-redshift-role'
DELIMITER '\t'
;
指定したS3パスの下にスライス数の整数倍のファイルが出力されます。例えば、ファイルの内容は以下のとおりです。先頭からレコードが出力されています。
3560 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 家具-本棚-10003403 家具 本棚 Dania コーナー シェルフ, 白 24918 30 2490 3561 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 家電-コピ-10003199 家電 コピー機 ヒューレット・パッカード ファックス, デジタル 85216 4 0 17888 3562 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 事務用-バイ-10004115 事務用品 バインダー Avery とじ機, 高耐久性 20136 6 0 3012 : :
[新機能] HEADERオプションの検証
HEADERオプション指定してUNLOAD
UNLOADコマンドにおいて、HEADERを指定すると先頭行にカラム名が設定された「ヘッダ行」が追加されます。このテーブルに対して、HEADERオプション指定のUNLOADコマンドを実行します。
UNLOAD ('SELECT * FROM cm_schema.orders')
TO 's3://cm-bucket/orders_unload/'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/cm-redshift-role'
DELIMITER '\t'
HEADER
;
テーブルのカラム名がヘッダ行に追加されます。指定したS3パスの下にスライス数の整数倍のファイルが出力されますが、それぞれのファイルにヘッダ行が追加されます。
row_id,order_id,order_date,ship_date,ship_mode,customer_id,customer_name,segment,country,city,state,region,product_id,category,sub_category,product_name,sales,quantity,discount,profit row_id order_id order_date ship_date ship_mode customer_id customer_name segment country city state region product_id category sub_category product_name sales quantity discount profit 3560 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 家具-本棚-10003403 家具 本棚 Dania コーナー シェルフ, 白 24918 30 2490 3561 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 家電-コピ-10003199 家電 コピー機 ヒューレット・パッカード ファックス, デジタル 85216 4 0 17888 : :
HEADERオプション指定してUNLOAD（日本語カラム）
次に任意のカラム名が指定できるか確認します。カラムにエイリアスをつけることで変更できます。
UNLOAD ('SELECT "row_id" as 行id ,"order_id" as オーダーid ,"order_date" as オーダー日 ,"ship_date" as 出荷日 ,"ship_mode" as 出荷モード ,"customer_id" as 顧客id ,"customer_name" as 顧客名 ,"segment" as 区分 ,"country" as 国 ,"city" as 市 ,"state" as 県 ,"region" as 地域 ,"product_id" as 商品id ,"category" as 分類 ,"sub_category" as サブ分類 ,"product_name" as 商品名 ,"sales" as 売上 ,"quantity" as 数量 ,"discount" as 割引率 ,"profit" as 利益 FROM cm_schema.orders;')
TO 's3://cm-bucket/orders_unload/'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/cm-redshift-role'
DELIMITER '\t'
HEADER
;
どうせ変更するならということで、日本語カラムにしましたが全く問題ありませんでした。
行id オーダーid オーダー日 出荷日 出荷モード 顧客id 顧客名 区分 国 市 県 地域 商品id 分類 サブ分類 商品名 売上 数量 割引率 利益 3560 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 家具-本棚-10003403 家具 本棚 Dania コーナー シェルフ, 白 24918 30 2490 3561 JP-2014-2034412 2014-01-06 2014-01-08 セカンド クラス 桃優-13840 桃木 優太 大企業 日本 福山 広島 中国地方 家電-コピ-10003199 家電 コピー機 ヒューレット・パッカード ファックス, デジタル 85216 4 0 17888 : :
これでデータレイクの連携がスムーズになる！?
デフォルトのUNLOADで作成したヘッダ行なしのファイルをAWS Glueでクロールするとカラム名がcol0,col1,col2..のように設定されます。Glueのコンソールからカラム名を変更することが可能ですが、カラム名が数百ある場合は気が遠くなるような作業です。
AWS Glueはヘッダ行を自動認識してカラム名に設定してくれます。これで、RedshiftからUNLOADしたファイルを何も変更も加えずにGlueでクロール、AthenaやRedshift Spectrumでクエリが実行できるようになりました。
最後に
Hadoopの世界では、.csvはヘッダを含まないものであり、ヘッダを含むものは.csvhであるという暗黙のルールがありますが、データレイクが民主化した今日においては、むしろヘッダ行付きが一般的になりつつあります。これまでRedshiftも、ヘッダ行付きファイルの読み込み（COPY）は対応していましたが、ヘッダ行付きファイルを出力（UNLOAD）するオプションがなく、COPY/UNLOADコマンドに対称性がないことが気になっていました。
Amazon Redshiftを中心としたDMPでは、SQLをインタフェースとして、射影、集計、フィルタした結果を、ファイル出力・データ連携しますが、今後は出力したい形式でUNLOADコマンドのSELECT文を書き換えることで、英数字・日本語問わずヘッダ行を追加できることが確認できました。UNLOADコマンドは、リーダーノードを介さず、コンピュートノードが直接S3に水平分散出力することで高速にファイル出力できますので積極的にご活用ください。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
